Imports System.Data.Common
Partial Class Presentation_Accounts_CheckPetrol
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Sql As String
        Dim Records As DbDataReader
        Dim Params(0) As GenParameter
        Dim CountSql As String

        Page.Title = "Check Petrol"

        If Not Page.IsPostBack Then

            'Populate the Executives List
            dlExecutives.DataTextField = "ExecutiveName"
            dlExecutives.DataValueField = "ExecutiveID"

            Sql = "SELECT ExecutiveID, ExecutiveName FROM ExecutiveMaster" & _
            " WHERE Active = 1 ORDER BY ExecutiveName"

            Records = GenHelper.ExecuteReader(Sql, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())
            dlExecutives.DataSource = Records
            dlExecutives.DataBind()
            Records.Close()

        End If

        'Sql = "SELECT ExecutiveMaster.ExecutiveName, MonthName(WorkflowMaster.MeetingDate) AS 'Month', Week(WorkflowMaster.MeetingDate) AS 'Week Number', ModeofTravel,SUM(DistanceTravelled.Distance) AS 'Total Distance'" & _
        '    " FROM (ProjectMaster INNER JOIN (ExecutiveMaster INNER JOIN (ClientMaster INNER JOIN WorkflowMaster ON ClientMaster.ClientID = WorkflowMaster.ClientID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID) ON ProjectMaster.ProjectID = WorkflowMaster.ProjectID) INNER JOIN DistanceTravelled ON WorkflowMaster.InteractionID = DistanceTravelled.InteractionID" & _
        '    " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID AND Year(WorkflowMaster.MeetingDate) = Year(CurDate())" & _
        '    " GROUP BY Week(WorkflowMaster.MeetingDate), MonthName(WorkflowMaster.MeetingDate), ModeofTravel" & _
        '    " ORDER BY WorkflowMaster.MeetingDate desc"

        'CountSql = "Select Count(*) from (SELECT ExecutiveMaster.ExecutiveName, Month(WorkflowMaster.MeetingDate) AS 'Month', Week(WorkflowMaster.MeetingDate) AS 'Week Number', ModeofTravel,SUM(DistanceTravelled.Distance) AS 'Total Distance'" & _
        '    " FROM (ProjectMaster INNER JOIN (ExecutiveMaster INNER JOIN (ClientMaster INNER JOIN WorkflowMaster ON ClientMaster.ClientID = WorkflowMaster.ClientID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID) ON ProjectMaster.ProjectID = WorkflowMaster.ProjectID) INNER JOIN DistanceTravelled ON WorkflowMaster.InteractionID = DistanceTravelled.InteractionID" & _
        '    " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID" & _
        '    " GROUP BY Week(WorkflowMaster.MeetingDate), Month(WorkflowMaster.MeetingDate), ModeofTravel" & _
        '    " ORDER BY MonthName(WorkflowMaster.MeetingDate)) as test"

        Sql = "SELECT ExecutiveMaster.ExecutiveName, MonthName(WorkflowMaster.MeetingDate) AS 'Month', Week(WorkflowMaster.MeetingDate) AS 'Week Number', ModeofTravel,SUM(DistanceTravelled.Distance) AS 'Total Distance'" & _
            " FROM (ProjectMaster INNER JOIN (ExecutiveMaster INNER JOIN (ClientMaster INNER JOIN WorkflowMaster ON ClientMaster.ClientID = WorkflowMaster.ClientID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID) ON ProjectMaster.ProjectID = WorkflowMaster.ProjectID) INNER JOIN DistanceTravelled ON WorkflowMaster.InteractionID = DistanceTravelled.InteractionID" & _
            " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID" & _
            " GROUP BY Week(WorkflowMaster.MeetingDate), MonthName(WorkflowMaster.MeetingDate), ModeofTravel" & _
            " ORDER BY WorkflowMaster.MeetingDate desc"

        CountSql = "Select Count(*) from (SELECT ExecutiveMaster.ExecutiveName, Month(WorkflowMaster.MeetingDate) AS 'Month', Week(WorkflowMaster.MeetingDate) AS 'Week Number', ModeofTravel,SUM(DistanceTravelled.Distance) AS 'Total Distance'" & _
            " FROM (ProjectMaster INNER JOIN (ExecutiveMaster INNER JOIN (ClientMaster INNER JOIN WorkflowMaster ON ClientMaster.ClientID = WorkflowMaster.ClientID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID) ON ProjectMaster.ProjectID = WorkflowMaster.ProjectID) INNER JOIN DistanceTravelled ON WorkflowMaster.InteractionID = DistanceTravelled.InteractionID" & _
            " WHERE WorkflowMaster.ExecutiveID = ?ExecutiveID" & _
            " GROUP BY Week(WorkflowMaster.MeetingDate), Month(WorkflowMaster.MeetingDate), ModeofTravel" & _
            " ORDER BY MonthName(WorkflowMaster.MeetingDate)) as test"


        Params(0) = New GenParameter("?ExecutiveID", dlExecutives.SelectedValue, Data.DbType.UInt32)

        cstmPetrol.Sql = Sql
        cstmPetrol.CountSql = CountSql
        cstmPetrol.ShowButton = True
        cstmPetrol.Params = Params

    End Sub
End Class
